import pymysql


class DBUtil(object):
    __conn = None
    __cursor = None

    @classmethod
    def __get_conn(cls):
        if cls.__conn is None:
            cls.__conn = pymysql.connect(host="192.168.40.130",
                                         port=3306,
                                         user="root",
                                         password="123456",
                                         database="School")
        return cls.__conn

    @classmethod
    def __get_cursor(cls):
        if cls.__cursor is None:
            cls.__cursor = cls.__get_conn().cursor()
        return cls.__cursor

    @classmethod
    def exe_sql(cls, sql):
        try:
            cursor = cls.__get_cursor()
            cursor.execute(sql)
            if sql.split()[0].lower() == "select":
                return cursor.fetchall()
            else:
                cls.__conn.commit()
                return cursor.rowcount
        except Exception as e:
            print(e)
            cls.__conn.rollback()
        finally:
            cls.__close_cursor()
            cls.__close_conn()

    @classmethod
    def __close_cursor(cls):
        if cls.__cursor:
            cls.__cursor.close()
            cls.__cursor = None

    @classmethod
    def __close_conn(cls):
        if cls.__conn:
            cls.__conn.close()
            cls.__conn = None


if __name__ == '__main__':
    create_teacher_sql = 'create table Teacher ( \
                        TId varchar(10), \
                        Tname varchar(10), \
                        CId varchar(10));'

    insert_teacher_sql = "insert into Teacher values \
                        ('01', '张三', '02'), \
                        ('02', '李四', '01'), \
                        ('03', '王五', '03'), \
                        ('04', '赵一', '02'), \
                        ('05', '钱二', '01'), \
                        ('06', '冯六', '02'), \
                        ('07', '陈七', NULL), \
                        ('08', '楚八', '01'), \
                        ('09', '魏九', '03');"

    # 创建Teacher表以及向表中插入9条数据
    DBUtil.exe_sql(create_teacher_sql)
    DBUtil.exe_sql(insert_teacher_sql)

    create_course_sql = "create table Course( \
                        CId varchar(10), \
                        Cname nvarchar(10) \
                        );"

    insert_course_sql = "insert into Course values \
                        ('01', '语文'), \
                        ('02', '数学'), \
                        ('03', '英语');"
    # 创建Course表向表中插入3条数据
    DBUtil.exe_sql(create_course_sql)
    DBUtil.exe_sql(insert_course_sql)

    # 查询没有授课的老师
    res = DBUtil.exe_sql('select * from Teacher where CId is null;')
    print(res)

    # 查询所有语文老师
    res = DBUtil.exe_sql(
        'select * from Teacher where CId=(select CId from Course where Cname="语文");')
    print(res)

    # 查询授课老师最少的学科
    res = DBUtil.exe_sql(
        'select Cname from Course where CId = (select CId, count(*) from Teacher group by CId limit 1);')
    print(res)
